package com.djzhou.gmms.util.excel;

import java.text.ParseException;

import org.apache.poi.hssf.usermodel.HSSFPrintSetup;

import wsepr.easypoi.excel.Excel;
import wsepr.easypoi.excel.editor.CellEditor;
import wsepr.easypoi.excel.editor.IPrintSetup;
import wsepr.easypoi.excel.editor.RowEditor;
import wsepr.easypoi.excel.editor.listener.CellValueListener;
import wsepr.easypoi.excel.style.Align;
import wsepr.easypoi.excel.style.BorderStyle;
import wsepr.easypoi.excel.style.Color;

/**
 * 导出EXCEL 通用化工具类
 * @author DJZHOU
 *
 */
public class ExcelUtil {
	 private static final String[] titles = {"姓名","年龄","时间"};

	 //sample data to fill the sheet.
	 private static final Object[][] data = {
	         {"大菊花", "12", "2013-10-10"},{"大菊花", "12", "2013-10-10"},{"大菊花", "12", "2013-10-10"},{"大菊花", "12", "2013-10-10"}
	         
	 };
	 
	 public static String exportExcel(String sheetName,String[] titles,int[] colsWidth,Object[][] data) throws Exception{
		 String filePath="D:/BusinessPlan.xls";
		 Excel excel = new Excel();
		//设置工作表样式
		excel.sheet().sheetName("Business Plan")//表名
			.displayGridlines(false)
			.printGridlines(false)
			.fitToPage(true)
			.horizontallyCenter(true)
			.autobreaks(true)
			.addCellValueListener(new CellListener())
			.printSetup(new IPrintSetup() {
				public void setup(HSSFPrintSetup printSetup) {
					printSetup.setLandscape(true);			        
			        printSetup.setFitHeight((short)1);
			        printSetup.setFitWidth((short)1);
				}
			});
		excel.row(0).value(titles).height(12.75f).align(Align.CENTER).bold().bgColor(Color.LIGHT_CORNFLOWER_BLUE);
		
		excel.sheet().freeze(1, 0);//冻结第一行
		
		int rownum = 1;
		RowEditor rowEditor = null;
        for (int i = 0; i < data.length; i++, rownum++) {
            if(data[i] == null) continue;
            int r = rownum + 1;
            rowEditor = excel.row(rownum);
            rowEditor.value(data[i])//写入一行数据
            	.borderFull(BorderStyle.THIN, Color.BLACK);//设置边框样式，细线黑色
        }
        
        //设置前3列的宽度
        excel.row(0).width(colsWidth);
        
        
        excel.saveExcel(filePath);//保存文件
        
		return filePath;
	 }
	 
	 
	 
	/**
	 * @param args
	 * @throws ParseException 
	 */
	public static void main(String[] args) throws ParseException {
		try {
			exportExcel("什么玩意",titles,new int[]{256*10, 256*20, 256*20},data);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void example(){
		Excel excel = new Excel();
		//设置工作表样式
		excel.sheet().sheetName("Business Plan")//表名
			.displayGridlines(false)
			.printGridlines(false)
			.fitToPage(true)
			.horizontallyCenter(true)
			.autobreaks(true)
			.addCellValueListener(new CellListener())
			.printSetup(new IPrintSetup() {
				public void setup(HSSFPrintSetup printSetup) {
					printSetup.setLandscape(true);			        
			        printSetup.setFitHeight((short)1);
			        printSetup.setFitWidth((short)1);
				}
			});
		
		excel.row(0).value(titles).height(12.75f)
			.align(Align.CENTER).bold().bgColor(Color.LIGHT_CORNFLOWER_BLUE);
		
		excel.sheet().freeze(1, 0);//冻结第一行
		int rownum = 1;
		RowEditor rowEditor = null;
        for (int i = 0; i < data.length; i++, rownum++) {
            if(data[i] == null) continue;
            int r = rownum + 1;
            String fmla = "=IF(AND(D"+r+",E"+r+"),E"+r+"+D"+r+",\"\")";//表达式
            rowEditor = excel.row(rownum);
            rowEditor.value(data[i])//写入一行数据
            	.borderFull(BorderStyle.THIN, Color.BLACK);//设置边框样式，细线黑色
            rowEditor.cell(5).value(fmla);//写入表达式
            if(i==0 || data[i-1] == null){
            	rowEditor.bold()//把整行加粗
            			.cell(1)//选择该行第2个单元格
            			.color(Color.DARK_BLUE)//设置字体颜色
            			.warpText(true);//自动换行
            	if(i==0){
            		rowEditor.cell(1).fontHeightInPoint(14);//设置字体大小，单位像素
            	}
            }
        }
        //设置前3列的宽度
        excel.row(0).width(new int[]{256*6, 256*33, 256*20});
        
        excel.column(3).align(Align.CENTER);//居中对齐
        excel.column(4,1).dataFormat("d-mmm")//设置日期格式
					   	.align(Align.RIGHT);//靠右对齐
        excel.column(5,1).bgColor(Color.GREY_25_PERCENT)//设置背景颜色，25%灰度
    					.align(Align.RIGHT)//靠右对齐
    					.dataFormat("d-mmm");//设置日期格式
        
        //分组
        excel.sheet().groupRow(4, 6).groupRow(9, 13).groupRow(16, 18);
        
		excel.saveExcel("E:/temp/excel/BusinessPlan.xls");//保存文件
	}
	
	private static class CellListener implements CellValueListener{

		public void onValueChange(CellEditor cell, Object newValue, int row,
				int col, Excel excel) {
			if(row >=1 && col>=6 && newValue != null){
				cell.bgColor(Color.BLUE).value("");//在这里设置单元格的值将不会触发监听器
			}
		}
		
	}

}
